package io.sdph.documents.dao;

import io.sdph.core.database.Transaction;
import io.sdph.core.database.exceptions.DatabaseException;
import io.sdph.documents.model.Invoice;
import io.sdph.documents.model.InvoiceType;
import io.sdph.documents.model.PaymentMethod;
import io.sdph.products.model.Product;
import io.sdph.userprofile.model.Address;
import io.sdph.userprofile.model.User;
import io.sdph.userprofile.model.UserPermission;
import io.sdph.userprofile.model.UserType;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

@Repository
public class InvoiceManagerDB implements InvoiceManagerDAO {
	
	// TODO sprobowac poprawic operacje na produktach i kupujacym (wykorzystac metody z User i Product Menager)
	
	private static final Logger log = Logger.getLogger(InvoiceManagerDB.class);
	
	@Override
	public Invoice getInvoice(final String number) {
		Invoice invoice = null;
		try {
			Transaction tr = new Transaction() {
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					ResultSet rs = select("SELECT number, idUserr, invoiceType, paymentMethod, price, creationDate, sellDate, deadlineDate FROM Invoice WHERE number = ?", number);
					Invoice in = null;
					if (rs.next()) {
						in = new Invoice(number);
						in.setType(InvoiceType.valueOf(rs.getString("invoiceType")));
						in.setPaymentMethod(PaymentMethod.valueOf(rs.getString("paymentMethod")));
						in.setPrice(rs.getDouble("price"));						
						in.setCreationDate(rs.getDate("creationDate"));
						in.setSellDate(rs.getDate("sellDate"));
						in.setDeadlineDate(rs.getDate("deadlineDate"));
						//////////////////
						User user = null;
						ResultSet rs2 = select(
								"select * from Userr where idUserr= ?",
								rs.getString("idUserr"));
						if (rs2.next()) {

							user = new User(rs2.getString("idUserr"));
							
							}
							in.setBuyer(user);
							//////////////////
						
							//////////////////
							Map<Product, Long> products= null;
							Product product=null;
							ResultSet rs5 = select(
									"select * from Products where number= ?",
									number);
							
							while(rs5.next()){
								
							
									product = new Product(rs5.getString("idProduct"));
									
			
								products.put(product, rs5.getLong("amount"));
							}
							
							
							in.setProducts(products);
							//////////////////
					}
					return in;
				}
			};
			invoice = (Invoice) tr.getResult();
		} catch (DatabaseException e) {
			log.error("Error while getting invoice [" + (number!=null ? number : "") +"] from DB.", e);
		}
		return invoice;
	}

	@Override
	public List<Invoice> getInvoices(User user) {
		
		ArrayList<Invoice> invoices = null;
		
		try {
			Transaction tr = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					ArrayList<Invoice> invoices = new ArrayList<Invoice>();
					
					//select * from Invoice
					
					ResultSet rs = select("select * from Invoice ");
					Invoice invoice=null;
					
					
					while (rs.next()){
						
						
						
						invoice = new Invoice(rs.getString("number"));
						invoice.setType(InvoiceType.valueOf(rs.getString("invoiceType")));
						invoice.setPaymentMethod(PaymentMethod.valueOf(rs.getString("paymentMethod")));
						invoice.setPrice(rs.getDouble("price"));						
						invoice.setCreationDate(rs.getDate("creationDate"));
						invoice.setSellDate(rs.getDate("sellDate"));
						invoice.setDeadlineDate(rs.getDate("deadlineDate"));
						//////////////////
						User user = null;
						ResultSet rs2 = select(
								"select * from Userr where idUserr= ?",
								rs.getString("idUserr"));
						if (rs2.next()) {

							user = new User(rs2.getString("idUserr"));
							
							}
						invoice.setBuyer(user);
							//////////////////
						
							//////////////////
							Map<Product, Long> products= null;
							Product product=null;
							ResultSet rs5 = select(
									"select * from Products where number= ?",
									rs.getString("number"));
							
							while(rs5.next()){
								
								
								
								
								
									product = new Product(rs5.getString("idProduct"));
									
								
								products.put(product, rs5.getLong("amount"));
							}
							
							
							invoice.setProducts(products);
							//////////////////
						
						
						invoices.add(invoice);
					}
					
					return invoices;
				}
			};
			invoices = (ArrayList<Invoice>) tr.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return invoices;
	}

	@Override
	public void saveInvoice(final Invoice invoice) {
		if (invoice.getNumber() != null ) {
			// update
			
			try {
				Transaction tr_updateInvo = new Transaction() {
					
					@Override
					public Object doIt() throws DatabaseException, SQLException {
						
						//Dodanie rekordu do Invoice
						
						update("UPDATE Invoice set idUserr=?, invoiceType=?, paymentMethod=?, price=?, creationDate=?, sellDate=?, deadlineDate=? where number=?;", invoice.getBuyer().getId(),
								invoice.getType().getName(),invoice.getPaymentMethod().getName(),invoice.getPrice(),invoice.getCreationDate(),invoice.getSellDate(),invoice.getDeadlineDate(),invoice.getNumber());
						
						
						//Usuniecie starych wartosci z Products
						delete("DELETE FROM Products WHERE number= ?;", invoice.getNumber());
						//Dodanie nowych wartosci do Products
						
						 
						 if (invoice.getProducts() != null ){
							for(Entry<Product, Long> entry : invoice.getProducts().entrySet()){
								Product key = entry.getKey(); 
								Long value = entry.getValue(); 
								
							
								insert("INSERT INTO Products VALUES (?,?,?);",invoice.getNumber(),key.getId(),value);
							
							}}
							
						
						
						return null;
					}
				};
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
			
			
		} else {
			// insert
			
			try {
				Transaction ts_insertInvo = new Transaction() {
					
					@Override
					public Object doIt() throws DatabaseException, SQLException {
						
						//Dodanie rekordu do Invoice
						int number = insert("INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES ('?','?','?','?','?','?','?');", invoice.getBuyer().getId(),
								invoice.getType().getName(),invoice.getPaymentMethod().getName(),invoice.getPrice(),invoice.getCreationDate(),invoice.getSellDate(),invoice.getDeadlineDate());
						//Dodanie rekordow do Products
						 if (invoice.getProducts() != null ){
								for(Entry<Product, Long> entry : invoice.getProducts().entrySet()){
									Product key = entry.getKey(); 
									Long value = entry.getValue(); 
									
								
									insert("INSERT INTO Products VALUES (?,?,?);",number,key.getId(),value);
								
								}}
						 
						return null;
					}
				};
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
	}

	@Override
	public void deleteInvoice(final Invoice invoice) {

		try {
			Transaction ts_delInvo = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {

					delete("DELETE FROM Invoice WHERE number= ?;", invoice.getNumber());
					delete("DELETE FROM Products WHERE number= ?;", invoice.getNumber());
					
					
					return null;
				}
			};
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public  void fill(final Invoice invoice) {
		try {
			Transaction tr = new Transaction() {
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					ResultSet rs = select("SELECT number, idUserr, invoiceType, paymentMethod, price, creationDate, sellDate, deadlineDate FROM Invoice WHERE number = ?", invoice.getNumber());
					
					if (rs.next()) {
						
						invoice.setType(InvoiceType.valueOf(rs.getString("invoiceType")));
						invoice.setPaymentMethod(PaymentMethod.valueOf(rs.getString("paymentMethod")));
						invoice.setPrice(rs.getDouble("price"));						
						invoice.setCreationDate(rs.getDate("creationDate"));
						invoice.setSellDate(rs.getDate("sellDate"));
						invoice.setDeadlineDate(rs.getDate("deadlineDate"));
						//////////////////
						User user = null;
						ResultSet rs2 = select(
								"select * from Userr where idUserr= ?",
								rs.getString("idUserr"));
						if (rs2.next()) {

							user = new User(rs2.getString("idUserr"));
							
							}
						invoice.setBuyer(user);
							//////////////////
						
							//////////////////
							Map<Product, Long> products= null;
							Product product=null;
							ResultSet rs5 = select(
									"select * from Products where number= ?",
									invoice.getNumber());
							
							while(rs5.next()){
								

									product = new Product(rs5.getString("idProduct"));
									
								
								products.put(product, rs5.getLong("amount"));
							}
							
							
							invoice.setProducts(products);
							//////////////////
					}
					return null;
				}
			};
			
		} catch (DatabaseException e) {
			log.error("Error while getting invoice [" + (invoice.getNumber()!=null ? invoice.getNumber() : "") +"] from DB.", e);
		}
		
	}

}
